﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class PositionService
    {
       
        public bool SearchTable()
        {
            SqlConnection conn = ConnPoll.GetConn();
            bool tempbool = false;
            string sql = "select * from Position";
            ConnPoll.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                tempbool = true;
                break;
            }
            dr.Close();
            com = null;
            ConnPoll.Close();
            return tempbool;
        }

        #region 创建职位表
        /// <summary>
        /// 创建职位表
        /// </summary>
        public void CreatePosition()
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                string tbna = "Position";
                string sql = " use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " PositionId int not null identity,"//主键Id
                    + " Explain nvarchar(100) null,"
                    + " PositionName nvarchar(100) not null"//职位名称
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_PositionId primary key (PositionId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
                Insert(new Position() { Explain="职位", PositionName=string.Empty });
            }
            catch (Exception)
            {

            }
        }
        #endregion


        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="p">职位表类</param>
        /// <returns></returns>
        public int Insert(Position p)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into Position (Explain,PositionName) values (@Explain,@PositionName)";
            if(p.Explain == string.Empty)
                sql= "insert into Position (Explain,PositionName) values (default,@PositionName)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@Explain", p.Explain),
                new SqlParameter("@PositionName", p.PositionName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="p">职位表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Delete(Position p)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete Position where PositionName=@PositionName";
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,new SqlParameter("@PositionName", p.PositionName));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="p">职位表类</param>
        /// <returns></returns>
        public int Set(Position p,string setStr)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update Position set PositionName=@setStr where PositionName=@PositionName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@setStr",setStr),
                new SqlParameter("@PositionName",p.PositionName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查找信息
        /// <summary>
        /// 查找所有信息
        /// </summary>
        /// <returns></returns>
        public List<Position> SearchAll()
        {
            SqlConnection conn = ConnPoll.GetConn();
            List<Position> lpt = new List<Position>();
            try
            {
                string sql = "select * from Position";
                SqlDataReader dr = SQLHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql);
                while (dr.Read())
                {
                    if (dr["PositionName"].ToString() == string.Empty)
                        continue;
                    Position pt = new Position();
                    pt.PositionId = Convert.ToInt32(dr["PositionId"]);
                    pt.PositionName = dr["PositionName"].ToString();
                    lpt.Add(pt);
                }
                dr.Close();
            }
            catch (Exception)
            {
                
            }
            return lpt;
        }
        #endregion

        #region 查询标题
        /// <summary>
        /// 查询标题
        /// </summary>
        /// <returns></returns>
        public string SearchTitle()
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select Explain from Position";
            return SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql).ToString();
        }
        #endregion

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name="sh"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Search(Position pt)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) form ShoppingHobby where PositionName=@PositionName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@PositionName", pt.PositionName)));
        }
        #endregion
    }
}
